/**   
* @ProjectName: ShopSytem
* @Title: BaseDao.java 
* @Package com.element5th.shop.comm 
* @Description: 所有DAO共同方法
* @author 第五元素小组   
* @date 2012-10-26 9:55:50 
* @version V1.0   
*/
package com.element5th.shop.comm;

import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.element5th.comm.DBHelper;
import com.element5th.comm.StringUtil;
import com.element5th.comm.TableColumn;
import com.element5th.comm.TypeUtil;

/**
 * The Class BaseDao.
 */
public abstract class BaseDao {
	
	
	/** The thread conn. */
	private ThreadLocal<Connection> threadConn = new ThreadLocal<Connection>();
	
	/**
	 * 打开线程数据库连接 Open connection.
	 * 
	 * @throws SystemException
	 *             the system exception
	 */
	protected void openConnection() throws SystemException {
		Connection conn = DBHelper.getConnection();
		threadConn.set(conn);
	}

	/**
	 * Gets the connection.
	 * 
	 * @return the connection
	 */
	protected Connection getConnection() {
		Connection conn = threadConn.get();
		return conn;
	}
	
	/**
	 * 关闭数据库预编译对象及结果集对象.
	 * 
	 * @param ps
	 *            the ps
	 * @param rs
	 *            the rs
	 * @throws SystemException
	 *             the system exception
	 */
	protected void close(PreparedStatement ps, ResultSet rs)
				throws SystemException {
		try {
			if (rs != null) {
				rs.close();
			}
			if (ps != null) {
				ps.close();
			}
		} catch (Exception e) {
			throw new SystemException(2001, "数据库关闭失败！"+e.getMessage());
		}
	}
	
	/**
	 * Rollback.
	 */
	protected void rollback(){
		Connection conn = this.getConnection();
		try {
			conn.rollback();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		DBHelper.closeConnection(conn);
	}
	
	/**
	 * Close connection.
	 * 
	 * @throws SystemException
	 *             the system exception
	 */
	protected void commit()
			throws SystemException {
		Connection conn = threadConn.get();
		try {
			conn.commit();
		} catch (SQLException e) {
			throw new SystemException(2004, "数据库事务提交失败！"+e.getMessage());
		}
		DBHelper.closeConnection(conn);
	}
	
	/**
	 * 从实体对象中提取出非空的字段，插入到数据库相应的表中.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param t
	 *            要保存入数据库的实体
	 * @return true, if successful
	 * @throws SystemException
	 *             the system exception
	 */
	protected <T> boolean save(T t) throws SystemException {
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) t.getClass();
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		/*得到类中所有字段*/
		Method[] methods = clazz.getDeclaredMethods();
		List<TableColumn> list = new ArrayList<TableColumn>();
		for (Method method : methods) {
			if(method.getName().indexOf("get") > -1) {
				if(method.getReturnType().equals(List.class) == false) {
					try {
						Object obj = method.invoke(t);
						if(obj != null){
							obj = TypeUtil.check(obj, null);
							TableColumn tc = new TableColumn(StringUtil
									.entity2Table(method.getName().substring(3)), obj);
							list.add(tc);
						}
					} catch (Exception e) {
						throw new SystemException(2002, "通过反射获取实体属性出错！"+e.getMessage());
					}
				}
			}
		}
		StringBuffer strBuf = new StringBuffer("insert into ");
		strBuf.append(tableName + "(");
		for (int i = 0; i < list.size()-1; i++) {
			strBuf.append(list.get(i).getColumnName() + ",");
		}
		strBuf.append(list.get(list.size()-1).getColumnName() + ") values(");
		for (int i = 0; i < list.size()-1; i++) {
			strBuf.append("?,");
		}
		strBuf.append( "?)" );
		try {
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i+1, list.get(i).getValue());
			}
			if(ps.executeUpdate() > 0){
				this.close(ps, null);
				return true;
			}
		} catch (SQLException e) {
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		}
		this.close(ps, null);
		return false;
	}
	
	
	/**
	 * 从实体对象中提取出非空的字段，通过ID更新入数据库中.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param t
	 *            要更新的实体对象
	 * @param id
	 *            要更新的对象的ID
	 * @return true, if successful
	 * @throws SystemException
	 *             the system exception
	 */
	protected <T> boolean update(T t, int id) throws SystemException {
		Connection conn = this.getConnection();
		ResultSet rs = null;
		PreparedStatement ps = null;
		
		String idName = null;
		
		@SuppressWarnings("unchecked")
		Class<T> clazz = (Class<T>) t.getClass();
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		/*得到类中所有字段*/
		Method[] methods = clazz.getDeclaredMethods();
		List<TableColumn> list = new ArrayList<TableColumn>();
		for (int i = 0; i < methods.length; i++) {
			if(methods[i].getName().indexOf("get") > -1) {
				if(methods[i].getReturnType().equals(List.class) == false &&
						(!StringUtil.entity2Table(methods[i].getName().substring(3)).equalsIgnoreCase(idName))) {
					try {
						Object obj = methods[i].invoke(t);
						if(obj != null ){
							obj = TypeUtil.check(obj, null);
							TableColumn tc = new TableColumn(StringUtil
									.entity2Table(methods[i].getName().substring(3)), obj);
							list.add(tc);
						}
					} catch (Exception e) {
						throw new SystemException(2002, "通过反射获取实体属性出错！"+e.getMessage());
					}
				}
			}
		}
		try {
			rs = conn.getMetaData().getPrimaryKeys(null, null, tableName);
			if(rs.next()) {
				idName = rs.getString("COLUMN_NAME");
			}
		} catch (SQLException e1) {
			throw new SystemException(2003, "执行SQL语句出错！"+e1.getMessage());
		}
		StringBuffer strBuf = new StringBuffer("update ");
		strBuf.append(tableName + " set ");
		for (int i = 0; i < list.size()-1; i++) {
			strBuf.append(list.get(i).getColumnName() + "=?,");
		}
		strBuf.append(list.get(list.size()-1).getColumnName() + "=? where ");
		strBuf.append(idName + "=? ");
		try {
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			for (int i = 0; i < list.size(); i++) {
				ps.setObject(i+1, list.get(i).getValue());
			}
			ps.setObject(list.size()+1, id);
			if(ps.executeUpdate() > 0){
				this.close(ps, rs);
				return true;
			}
		} catch (SQLException e) {
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		}
		this.close(ps, rs);
		return false;
	}
	
	/**
	 * 根据ID删除对应数据库中的数据.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param clazz
	 *            要删除的实体类型
	 * @param id
	 *            要删除的对象的ID
	 * @return true, if successful
	 * @throws SystemException
	 *             the system exception
	 */
	protected <T> boolean delete(Class<T> clazz, int id) throws SystemException {
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		String idName = null;
		
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		try {
			rs = conn.getMetaData().getPrimaryKeys(null, null, tableName);
			if(rs.next()) {
				idName = rs.getString("COLUMN_NAME");
			}
			StringBuffer strBuf = new StringBuffer("delete from " + tableName + "where " + idName + "=? ");
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			ps.setObject(1, id);
			if(ps.executeUpdate() > 0) {
				this.close(ps, rs);
				return true;
			}
		} catch (SQLException e) {
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		}
		
		this.close(ps, rs);
		return false;
	}
	
	
	
	/**
	 * 通过ID在数据库中查找某个对象.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param clazz
	 *            查找的实体类型
	 * @param id
	 *            the id
	 * @return 查找到的实体对象
	 */
	protected <T> T findById(Class<T> clazz, int id) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String idName = null;
		T t = null;
		
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		try {
			/*获得主键列名*/
			conn = this.getConnection();
			idName = DBHelper.getPKName(conn, tableName);
			StringBuffer strBuf = new StringBuffer(" select * from " + tableName + " where " + idName + "=? ");
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			ps.setInt(1, id);
			rs = ps.executeQuery();
			if(rs.next()) {
				t = clazz.newInstance();
				Method[] methods = clazz.getDeclaredMethods();
				ResultSetMetaData metaData = rs.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					for (int j = 0; j < methods.length; j++) {
						if(methods[j].getName().indexOf("set") > -1 && 
								StringUtil.entity2Table( methods[j].getName().substring(3) )
								.equalsIgnoreCase(metaData.getColumnName(i+1))) {
							Object obj = rs.getObject(i+1);
							obj = TypeUtil.check(obj, methods[j].getParameterTypes()[0]);
							methods[j].invoke(t, obj);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
		this.close(ps, rs);
		return t;
	}
	
	
	/**
	 * 查找数据库中某种类型的所有对象.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param clazz
	 *            查找的实体类型
	 * @return 查找到的实体对象列表
	 */
	protected <T> List<T> findAll(Class<T> clazz) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String idName = null;
		T t = null;
		List<T> list = new ArrayList<T>();
		
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		try {
			conn = this.getConnection();
			idName = DBHelper.getPKName(conn, tableName);
			StringBuffer strBuf = new StringBuffer(" select * from " + tableName + " order by " + idName);
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			rs = ps.executeQuery();
			while(rs.next()) {
				t = clazz.newInstance();
				Method[] methods = clazz.getDeclaredMethods();
				ResultSetMetaData metaData = rs.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					for (int j = 0; j < methods.length; j++) {
						if(methods[j].getName().indexOf("set") > -1 && 
								StringUtil.entity2Table( methods[j].getName().substring(3) )
								.equalsIgnoreCase(metaData.getColumnName(i+1))) {
							Object obj = rs.getObject(i+1);
							obj = TypeUtil.check(obj, methods[j].getParameterTypes()[0]);
							methods[j].invoke(t, obj);
						}
					}
				}
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
		this.close(ps, rs);
		return list;
	}
	
	
	/**
	 * Find by rs.
	 * 
	 * @param <T>
	 *            the generic type
	 * @param clazz
	 *            the clazz
	 * @param rs
	 *            the rs
	 * @return the t
	 */
	protected <T> T findBySql(Class<T> clazz, String sql) {
		T t = null;
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			System.out.println(sql);
			rs = ps.executeQuery();
			if(rs.next()) {
				t = clazz.newInstance();
				Method[] methods = clazz.getDeclaredMethods();
				ResultSetMetaData metaData = rs.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					for (int j = 0; j < methods.length; j++) {
						if(methods[j].getName().indexOf("set") > -1 && 
								StringUtil.entity2Table( methods[j].getName().substring(3) )
								.equalsIgnoreCase(metaData.getColumnName(i+1))) {
							Object obj = rs.getObject(i+1);
							obj = TypeUtil.check(obj, methods[j].getParameterTypes()[0]);
							methods[j].invoke(t, obj);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		this.close(ps, rs);
		return t;
	}
	
	/**
	 * Find list by rs.
	 * 
	 * @param <T>
	 *            the generic type
	 * @param clazz
	 *            the clazz
	 * @param rs
	 *            the rs
	 * @return the list
	 */
	protected <T> List<T> findListBySql(Class<T> clazz, String sql) {
		T t = null;
		List<T> list = new ArrayList<T>();
		Connection conn = this.getConnection();
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			ps = conn.prepareStatement(sql);
			System.out.println(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				t = clazz.newInstance();
				Method[] methods = clazz.getDeclaredMethods();
				ResultSetMetaData metaData = rs.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					for (int j = 0; j < methods.length; j++) {
						if(methods[j].getName().indexOf("set") > -1 && 
								StringUtil.entity2Table( methods[j].getName().substring(3) )
								.equalsIgnoreCase(metaData.getColumnName(i+1))) {
							Object obj = rs.getObject(i+1);
							obj = TypeUtil.check(obj, methods[j].getParameterTypes()[0]);
							methods[j].invoke(t, obj);
						}
					}
				}
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
		return list;
	}
	
	/**
	 * 按照属性的名称在数据库查找某个对象.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param clazz
	 *            查找的实体类型
	 * @param colName
	 *            作为查找条件的属性名
	 * @param key
	 *            查找条件
	 * @return 查找到的实体对象
	 */
	protected <T> T findByCol(Class<T> clazz, String colName, Object key) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		T t = null;
		//得到类名
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		try {
			conn = this.getConnection();
			colName = StringUtil.entity2Table(colName);
			StringBuffer strBuf = new StringBuffer(" select * from " + tableName + " where " + colName + "=? ");
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			ps.setObject(1, key);
			rs = ps.executeQuery();
			if(rs.next()) {
				t = clazz.newInstance();
				Method[] methods = clazz.getDeclaredMethods();
				ResultSetMetaData metaData = rs.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					for (int j = 0; j < methods.length; j++) {
						if(methods[j].getName().indexOf("set") > -1 && 
								StringUtil.entity2Table( methods[j].getName().substring(3) )
								.equalsIgnoreCase(metaData.getColumnName(i+1))) {
							Object obj = rs.getObject(i+1);
							obj = TypeUtil.check(obj, methods[j].getParameterTypes()[0]);
							methods[j].invoke(t, obj);
						}
					}
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
		this.close(ps, rs);
		return t;
	}
	
	/**
	 * 按照属性的名称在数据库查找某个对象.
	 * 
	 * @param <T>
	 *            泛型，代表实体类型
	 * @param clazz
	 *            查找的实体类型
	 * @param colName
	 *            作为查找条件的属性名
	 * @param key
	 *            查找条件
	 * @return 查找到的实体对象
	 */
	protected <T> List<T> findListByCol(Class<T> clazz, String colName, Object key) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		String idName = null;
		T t = null;
		List<T> list = new ArrayList<T>();
		
		String className = clazz.getSimpleName();
		/*对User进行处理，以便能正常得出表名*/
		if(className.equals("User"))
			className = "UserTable";
		/*将实体类名转化为对应表的表名*/
		String tableName = StringUtil.entity2Table(className);
		try {
			conn = this.getConnection();
			idName = DBHelper.getPKName(conn, tableName);
			colName = StringUtil.entity2Table(colName);
			StringBuffer strBuf = new StringBuffer(" select * from " + tableName + " where " + colName + "=? ");
			strBuf.append(" order by " + idName);
			ps = conn.prepareStatement(strBuf.toString());
			System.out.println(strBuf.toString());
			ps.setObject(1, key);
			rs = ps.executeQuery();
			while(rs.next()) {
				t = clazz.newInstance();
				Method[] methods = clazz.getDeclaredMethods();
				ResultSetMetaData metaData = rs.getMetaData();
				for (int i = 0; i < metaData.getColumnCount(); i++) {
					for (int j = 0; j < methods.length; j++) {
						if(methods[j].getName().indexOf("set") > -1 && 
								StringUtil.entity2Table( methods[j].getName().substring(3) )
								.equalsIgnoreCase(metaData.getColumnName(i+1))) {
							Object obj = rs.getObject(i+1);
							obj = TypeUtil.check(obj, methods[j].getParameterTypes()[0]);
							methods[j].invoke(t, obj);
						}
					}
				}
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new SystemException(2003, "执行SQL语句出错！"+e.getMessage());
		} catch (InstantiationException e1) {
			e1.printStackTrace();
		} catch (IllegalAccessException e1) {
			e1.printStackTrace();
		} catch (IllegalArgumentException e) {
			e.printStackTrace();
		} catch (InvocationTargetException e) {
			e.printStackTrace();
		}
		
		this.close(ps, rs);
		return list;
	}
	
}
